const models = require('../db/mysqldb/index')
const { Op } = require("sequelize");

class DataSqlModel {
  /**
   * @description 根据name查找,不包含删除数据
   * @param {string} name 
   * @returns 
   */
  static async findByName (name) {
    return await models.data_sql.findOne({
      where: {
        name,
        delete_timestamp: {
          [Op.is]: null,
        }
      }
    })
  }
  /**
   * @description 通过name和id查找数据，不包含删除数据
   * @param {string} name 
   * @param {string} data_sql_id 
   * @returns 
   */
  static async findByNameAndId (name, data_sql_id,) {
    return await models.data_sql.findOne({
      where: {
        name,
        delete_timestamp: {
          [Op.is]: null,
        },
        data_sql_id: {
          [Op.ne]: data_sql_id,
        }
      }
    })
  }
  /**
   * @description create
   * @param {*} name 
   * @param {*} data_source_id 
   * @param {*} code 
   * @param {*} remark 
   * @param {*} op_user_id 
   * @returns 
   */
  static async create (name, data_source_id, code, remark, op_user_id,) {
    return await models.data_sql.create({
      name, data_source_id, code, remark,
      create_by: op_user_id
    })
  }
  /**
   * @description update
   * @param {*} name 
   * @param {*} data_source_id 
   * @param {*} code 
   * @param {*} remark 
   * @param {*} op_user_id 
   * @returns 
   */
  static async update (name, data_source_id, code, remark, op_user_id, data_sql_id) {
    return await models.data_sql.update(
      {
        name, data_source_id, code, remark,
        update_by: op_user_id,
        update_timestamp: Date.now()
      },
      {
        where: { data_sql_id },
      }
    )
  }
  /**
  * @description delete
  * @param {*} op_user_id 
  * @param {*} data_source_id 
  * @returns 
  */
  static async delete (op_user_id, data_sql_id) {
    return await models.data_sql.update(
      {
        delete_timestamp: Date.now(),
        delete_by: op_user_id
      },
      {
        where: { data_sql_id },
      }
    )
  }
  /**
   * @description list
   * @param {*} limit 
   * @param {*} size 
   * @param {*} name 
   * @param {*} data_source_id 
   * @param {*} remark 
   * @returns 
   */
  static async list (limit, size = 1, name, data_source_id, remark,) {
    let sql = `select SQL_CALC_FOUND_ROWS data_sql.data_sql_id, data_sql.name, data_sql.code, data_sql.data_source_id, data_source.name as data_source_name,data_source.db_type as data_source_type, data_sql.update_by,data_sql.remark as remark,data_sql.update_timestamp,data_sql.create_by,data_sql.create_timestamp from data_sql LEFT JOIN data_source ON data_sql.data_source_id = data_source.data_source_id where 1 = 1 AND data_sql.delete_timestamp IS NULL AND data_source.delete_timestamp IS NULL `
    if (name) {
      sql = sql + `AND data_sql.name like '%${name}%' `
    }
    if (data_source_id) {
      sql = sql + `AND data_sql.data_source_id = '${data_source_id}' `
    }
    if (remark) {
      sql = sql + `AND data_sql.remark like '%${remark}%' `
    }
    if (limit) {
      sql = sql + `LIMIT ${limit} OFFSET  ${(size - 1) * limit} `
    }
    const result = await models.sequelize.query(sql, { replacements: ['active'], type: models.sequelize.QueryTypes.SELECT })
    const count = await models.sequelize.query(`SELECT FOUND_ROWS() as count;`, { replacements: ['active'], type: models.sequelize.QueryTypes.SELECT })
    return { result, count }
  }
  /**
   * @description allList
   * @returns 
   */
  static async allList () {
    let sql = `select SQL_CALC_FOUND_ROWS data_sql_id as value,name as label from data_sql where 1 = 1 AND delete_timestamp IS NULL`
    const result = await models.sequelize.query(sql, { replacements: ['active'], type: models.sequelize.QueryTypes.SELECT })
    return result
  }
  static async sqlDetail (data_sql_id) {
    let sql = `SELECT
      data_sql.code,
      data_sql.data_sql_id,
      data_source.host,
      data_source.port,
      data_source.db_name,
      data_source.user,
      data_source.password,
      data_source.db_type 
    FROM
      data_sql
      LEFT JOIN data_source ON data_source.data_source_id = data_sql.data_source_id 
    WHERE
      data_sql.data_sql_id = ${data_sql_id} `
    sql = sql + "ORDER BY data_sql.create_timestamp DESC ,data_sql.update_timestamp DESC "
    const sourceInfo = await models.sequelize.query(sql, { replacements: ['active'], type: models.sequelize.QueryTypes.SELECT })
    return sourceInfo
  }
}
module.exports = {
  DataSqlModel,
}